package com.olap.starter.util.sql;

import org.springframework.stereotype.Service;

@Service
public class SqlServerTaleSqlService implements TaleSqlService {

    // 获取表信息的 SQL 语句
    private String tableSql = "SELECT " +
            "TABLE_NAME AS value, " +
            "CONCAT(TABLE_NAME, '(', CAST(ep.value AS NVARCHAR(MAX)), ')') AS label " +
            "FROM " +
            "INFORMATION_SCHEMA.TABLES t " +
            "LEFT JOIN " +
            "sys.extended_properties ep ON ep.major_id = OBJECT_ID(t.TABLE_NAME) AND ep.minor_id = 0 AND ep.name = 'MS_Description' " +
            "WHERE " +
            "TABLE_CATALOG = '%s' " +
            "ORDER BY " +
            "TABLE_NAME asc";

    // 获取列信息的 SQL 语句
    private String columnSql = "SELECT " +
            "t.name AS table_name, " +
            "c.name AS column_name, " +
            "CASE WHEN c.is_nullable = 0 AND c.is_identity = 0 AND ic.column_id IS NULL THEN 1 ELSE NULL END AS is_required, " +
            "CASE WHEN ic.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_pk, " +
            "CASE WHEN ic.column_id IS NULL THEN 1 ELSE 0 END AS visible, " +
            "c.column_id AS sort, " +
            "CAST(ep.value AS NVARCHAR(MAX)) AS column_comment, " +
            "CASE WHEN c.is_identity = 1 THEN 1 ELSE 0 END AS is_increment, " +
            "c.system_type_id, " +
            "1 AS checked " +
            "FROM " +
            "sys.tables t " +
            "JOIN " +
            "sys.columns c ON t.object_id = c.object_id " +
            "LEFT JOIN " +
            "sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id AND ic.index_id IN (SELECT index_id FROM sys.indexes WHERE object_id = t.object_id AND is_primary_key = 1) " +
            "LEFT JOIN " +
            "sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description' " +
            "WHERE " +
            "t.name = '%s' AND t.schema_id = SCHEMA_ID('dbo') " +
            "ORDER BY " +
            "c.column_id";
    @Override
    public String getTablesSql(String dbName) {
        return String.format(tableSql, dbName);
    }

    @Override
    public String getColumnsSql(String dbName, String tableName) {
        return String.format(columnSql,tableName);
    }
}
